package com.geotmt.billingcenter.common.utils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @Descriptinon: Excel 导出类
 * @Author:       yanghanwei
 * @Mail:         yanghanwei@geotmt.com
 * @CreateDate:   2018/12/3 17:15
 * @Version:      v1
 */
public class ExportExcelUtils {

    private static final String[] IDENTITYSTRARR = {"本人","联系人"};

    private static final String ATTR_ARR_STR = "attrArr";
    private static final String DATA_LIST_STR = "dataList";
    private static final String TITLE_ARR_STR = "titleArr";
    private static final String SHEET_NAME_STR = "sheetName";
    private final static String sheetName = "Sheet1";

    private static FileOutputStream fout = null;
    private static OutputStream out = null;
	
	private static OutputStream setResponseHeaderFileName(HttpServletResponse response, String filename) throws IOException {
		response.setContentType("application/octet-stream;charset=utf-8");
		response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(filename+".xls","UTF-8"));
		OutputStream out = new BufferedOutputStream(response.getOutputStream());
		return out;
	}

    private static final Logger logger = LoggerFactory.getLogger(ExportExcelUtils.class);

    /**
     * 导出 exccel 工具类（多个sheet导出）
     * @param mapList
     * @param filePath
     * sheetName：sheet名字
     * titleArr：表头中文名数组
     * attrArr：数据对应的实体类属性数组（与表头一一对应）
     * dataList：到处的数据list
     */
    public static void buildExcelTemplate( List<Map<String, Object>> mapList,String filePath) {
        HSSFWorkbook wb = new HSSFWorkbook();
        FileOutputStream fout = null;
        try{
            // 将文件存到指定位置
            fout = new FileOutputStream(filePath);
            if (!mapList.isEmpty()) {
                for (int a = 0; a < mapList.size(); a++) {
                    String sheetName = "Sheet1";
                    if(!StringUtils.isEmpty(mapList.get(a).get("sheetName"))){
                        sheetName = String.valueOf(mapList.get(a).get("sheetName"));
                    }
                    String[] titleArr = (String[]) mapList.get(a).get("titleArr");
                    String[] attrArr = (String[]) mapList.get(a).get("attrArr");
                    Object dataList = mapList.get(a).get("dataList");
                    // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
                    HSSFSheet sheetMonitor = wb.createSheet(sheetName);
                    // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
                    HSSFRow rowMonitor = sheetMonitor.createRow((int) 0);
                    // 第四步，创建单元格，并设置值表头 设置表头居中
                    HSSFCellStyle style = wb.createCellStyle();

                    //水平居中
                    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                    //垂直居中
                    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                    // 设置边框
                    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
                    style.setBorderTop(HSSFCellStyle.BORDER_THIN);

                    HSSFCell cellMonitor = rowMonitor.createCell((int) 0);
                    for (int i = 0; i < titleArr.length; i++) {
                        cellMonitor.setCellValue(titleArr[i]);
                        cellMonitor.setCellStyle(style);
                        cellMonitor = rowMonitor.createCell((int) (i + 1));
                    }
                    String jsonString = JSONObject.toJSONString(dataList);
                    JSONArray jsonArray = JSONObject.parseArray(jsonString);
                    if (jsonArray != null && !jsonArray.isEmpty()) {
                        for (int i = 0; i < jsonArray.size(); i++) {
                            Map<String, Object> map = JSONObject.parseObject(JSONObject.toJSONString(jsonArray.get(i)), Map.class);
                            for (int j = 0; j < titleArr.length; j++) {
                                rowMonitor = sheetMonitor.createRow((int) i + 1);
                                // 第五步，写入实体数据 实际应用中这些数据从数据库得到，
                                for (int k = 0; k < attrArr.length; k++) {
                                    if (map.containsKey(attrArr[k]) && !StringUtils.isEmpty(map.get(attrArr[k]))) {
                                        // 第四步，创建单元格，并设置值
                                        if(!StringUtils.isEmpty(map.get(attrArr[k]))){
                                            String value = String.valueOf(map.get(attrArr[k]));
                                            rowMonitor.createCell((int) k).setCellValue(value);
                                        }else{
                                            rowMonitor.createCell((int) k).setCellValue("");
                                        }
//                                        String value = String.valueOf(map.get(attrArr[k]));
                                        /*if (isIntNum(value)) {
                                            rowMonitor.createCell((int) k).setCellValue(Integer.parseInt(value));
                                        } else if (isDoubleNum(value)) {
                                            rowMonitor.createCell((int) k).setCellValue(Double.parseDouble(value));
                                        } else {
                                            rowMonitor.createCell((int) k).setCellValue(String.valueOf(map.get(attrArr[k])));
                                        }*/
                                    }
                                }
                            }
                        }
                    }
                }
            }
            wb.write(fout);
        }catch (Exception e){
            e.printStackTrace();
            logger.info("导出Excel失败");
        }finally {
            try {
                fout.close();
            }catch (Exception e){
            }
        }
    }

    /**
     * 导出 exccel 工具类（1个sheet导出）
     * @param dataListForMap
     * sheetName：sheet名字
     * titleArr：表头中文名数组
     * attrArr：数据对应的实体类属性数组（与表头一一对应）
     * dataListForMap：导出的数据list
     */
    public static void buildExcelTemplate(Map<String, Object> dataListForMap, HttpServletResponse response, String fileName) {
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheetMonitor = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow rowMonitor = sheetMonitor.createRow(0);
        try{
            out = setResponseHeaderFileName(response,fileName);
            //创建excel标题
            createTitle(wb,rowMonitor,dataListForMap);
            //写入数据
            writeData(sheetMonitor,1,dataListForMap);
            wb.write(out);
        }catch (Exception e){
            logger.info("导出Excel失败,Exception:{}",e);
        }finally {
            try {
                out.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    /**
     * 创建excel标题
     * @param dataListForMap
     */
    private static void createTitle(HSSFWorkbook wb,HSSFRow rowMonitor,Map<String, Object> dataListForMap){
        String[] titleArr = (String[]) dataListForMap.get(TITLE_ARR_STR);

        //居中样式
        HSSFCellStyle centerStyle = centerStyle(wb);

        for (int i = 0; i < titleArr.length; i++) {
            HSSFCell cellMonitor = rowMonitor.createCell(i);
            cellMonitor.setCellStyle(centerStyle);
            cellMonitor.setCellValue(titleArr[i]);
        }
    }

    /**
     * 文本格式
     * @param wb
     * @return
     */
    private static HSSFCellStyle textStyle(HSSFWorkbook wb){
        //设置单元格为文本格式
        HSSFCellStyle textStyle = wb.createCellStyle();
        HSSFDataFormat textFormat = wb.createDataFormat();
        textStyle.setDataFormat(textFormat.getFormat("@"));
        // 居中
        textStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        return textStyle;
    }

    /**
     * 必填字体
     * @return
     */
    private static HSSFCellStyle redStyle(HSSFWorkbook wb){
        HSSFFont fontRed = wb.createFont();
        fontRed.setFontHeightInPoints((short) 10);
        fontRed.setColor(HSSFColor.RED.index);
        fontRed.setFontName("微软雅黑");
        //必填单元格格式
        HSSFCellStyle styleRed = wb.createCellStyle();
        styleRed.setFont(fontRed);
        styleRed.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        styleRed.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //水平居中
        styleRed.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //垂直居中
        styleRed.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleRed.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleRed.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleRed.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleRed.setBorderRight(HSSFCellStyle.BORDER_THIN);
        return styleRed;
    }

    /**
     * 居中样式
     */
    private static HSSFCellStyle centerStyle(HSSFWorkbook wb){
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle centerStyle = wb.createCellStyle();
        //水平居中
        centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //垂直居中
        centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置边框
        centerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        centerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        centerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        centerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        return centerStyle;
    }

    /**
     * 写入数据
     * @param sheetMonitor
     * @param startRow
     * @param dataListForMap
     */
    public static void writeData(HSSFSheet sheetMonitor,Integer startRow,Map<String, Object> dataListForMap){
        String[] titleArr = (String[]) dataListForMap.get(TITLE_ARR_STR);
        String[] attrArr = (String[]) dataListForMap.get(ATTR_ARR_STR);
        Object dataList = dataListForMap.get(DATA_LIST_STR);

        String jsonString = JSONObject.toJSONString(dataList);
        JSONArray jsonArray = JSONObject.parseArray(jsonString);
        if (jsonArray != null && !jsonArray.isEmpty()) {
            for (int i = 0; i < jsonArray.size(); i++) {
                Map<String, Object> map = JSONObject.parseObject(JSONObject.toJSONString(jsonArray.get(i)), Map.class);
                for (int j = 0; j < titleArr.length; j++) {
                    HSSFRow rowMonitor = sheetMonitor.createRow( i + startRow);
                    // 第五步，写入实体数据 实际应用中这些数据从数据库得到，
                    for (int k = 0; k < attrArr.length; k++) {
                        if (map != null && attrArr[k] != null && map.containsKey(attrArr[k]) && !StringUtils.isEmpty(map.get(attrArr[k]))) {
                            // 第四步，创建单元格，并设置值
                            if(!StringUtils.isEmpty(map.get(attrArr[k]))){
                                String value = String.valueOf(map.get(attrArr[k]));
                                rowMonitor.createCell((int) k).setCellValue(value);
                            }else{
                                rowMonitor.createCell((int) k).setCellValue("");
                            }
                        }
                    }
                }
            }
        }
    }

    /**
     * 判断是否是整数
     * @param str
     * @return
     */
    public static boolean isIntNum(String str) {
        Pattern pattern = Pattern.compile("^\\d+$");
        Matcher isIntNum = pattern.matcher(str);
        if (!isIntNum.matches()) {
            return false;
        }
        return true;
    }


    /**
     * 判断是否是浮点数
     * @param str
     * @return
     */
    public static boolean isDoubleNum(String str) {
        Pattern pattern = Pattern.compile("\\d+\\.\\d+$|-\\d+\\.\\d+$");
        Matcher isDoubleNum = pattern.matcher(str);
        if (!isDoubleNum.matches()) {
            return false;
        }
        return true;
    }
}
